package pers.cz.sql;

import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.BinaryExpression;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.LongValue;
import net.sf.jsqlparser.expression.StringValue;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.expression.operators.relational.*;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.parser.SimpleNode;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.alter.Alter;
import net.sf.jsqlparser.statement.create.index.CreateIndex;
import net.sf.jsqlparser.statement.create.table.CreateTable;
import net.sf.jsqlparser.statement.create.view.CreateView;
import net.sf.jsqlparser.statement.delete.Delete;
import net.sf.jsqlparser.statement.drop.Drop;
import net.sf.jsqlparser.statement.execute.Execute;
import net.sf.jsqlparser.statement.insert.Insert;
import net.sf.jsqlparser.statement.merge.Merge;
import net.sf.jsqlparser.statement.replace.Replace;
import net.sf.jsqlparser.statement.select.*;
import net.sf.jsqlparser.statement.truncate.Truncate;
import net.sf.jsqlparser.statement.update.Update;
import net.sf.jsqlparser.statement.upsert.Upsert;
import net.sf.jsqlparser.util.TablesNamesFinder;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.StringReader;
import java.util.*;

/**
 * @program: parser
 * @description: SqlParserTool Sql解析工具
 * @author: Cheng Zhi
 * @create: 2021-05-28 00:04
 **/
public class SqlParserTool {

    static Logger logger = LoggerFactory.getLogger(SqlParserTool.class);

    /**
     * 由于jsqlparser没有获取SQL类型的原始工具，并且在下面操作时需要知道SQL类型，所以编写此工具方法
     * @param sql sql语句
     * @return sql类型，
     * @throws JSQLParserException
     */
    public static SqlType getSqlType(String sql)  {
        Statement sqlStmt = null;
        try {
            sqlStmt = CCJSqlParserUtil.parse(new StringReader(sql));
        } catch (Exception e) {
            logger.error("sql parser failed!", e);
        }
        if (sqlStmt instanceof Alter) {
            return SqlType.ALTER;
        } else if (sqlStmt instanceof CreateIndex) {
            return SqlType.CREATEINDEX;
        } else if (sqlStmt instanceof CreateTable) {
            return SqlType.CREATETABLE;
        } else if (sqlStmt instanceof CreateView) {
            return SqlType.CREATEVIEW;
        } else if (sqlStmt instanceof Delete) {
            return SqlType.DELETE;
        } else if (sqlStmt instanceof Drop) {
            return SqlType.DROP;
        } else if (sqlStmt instanceof Execute) {
            return SqlType.EXECUTE;
        } else if (sqlStmt instanceof Insert) {
            return SqlType.INSERT;
        } else if (sqlStmt instanceof Merge) {
            return SqlType.MERGE;
        } else if (sqlStmt instanceof Replace) {
            return SqlType.REPLACE;
        } else if (sqlStmt instanceof Select) {
            return SqlType.SELECT;
        } else if (sqlStmt instanceof Truncate) {
            return SqlType.TRUNCATE;
        } else if (sqlStmt instanceof Update) {
            return SqlType.UPDATE;
        } else if (sqlStmt instanceof Upsert) {
            return SqlType.UPSERT;
        } else {
            return SqlType.NONE;
        }
    }

    /**
     * 获取sql操作接口,与上面类型判断结合使用
     * example:
     * String sql = "create table a(a string)";
     * SqlType sqlType = SqlParserTool.getSqlType(sql);
     * if(sqlType.equals(SqlType.SELECT)){
     *     Select statement = (Select) SqlParserTool.getStatement(sql);
     *  }
     * @param sql
     * @return
     * @throws JSQLParserException
     */
    public static Statement getStatement(String sql) throws JSQLParserException {
        Statement sqlStmt = CCJSqlParserUtil.parse(new StringReader(sql));
        return sqlStmt;
    }

    /**
     * 获取tables的表名
     * @param statement
     * @return
     */
    public static List<String> getTableList(Select statement){
        TablesNamesFinder tablesNamesFinder = new TablesNamesFinder();
        List<String> tableList = tablesNamesFinder.getTableList(statement);
        return tableList;
    }

    /**
     * 获取join层级
     * @param selectBody
     * @return
     */
    public static List<Join> getJoins(SelectBody selectBody){
        if(selectBody instanceof PlainSelect){
            List<Join> joins =((PlainSelect) selectBody).getJoins();
            return joins;
        }
        return new ArrayList<Join>();
    }

    /**
     *
     * @param selectBody
     * @return
     */
    public static List<Table> getIntoTables(SelectBody selectBody){
        if(selectBody instanceof PlainSelect){
            List<Table> tables = ((PlainSelect) selectBody).getIntoTables();
            return tables;
        }
        return new ArrayList<Table>();
    }

    /**
     *
     * @param selectBody
     * @return
     */
    public static void setIntoTables(SelectBody selectBody,List<Table> tables){
        if(selectBody instanceof PlainSelect){
            ((PlainSelect) selectBody).setIntoTables(tables);
        }
    }

    /**
     * 获取limit值
     * @param selectBody
     * @return
     */
    public static Limit getLimit(SelectBody selectBody){
        if(selectBody instanceof PlainSelect){
            Limit limit = ((PlainSelect) selectBody).getLimit();
            return limit;
        }
        return null;
    }

    /**
     * 为SQL增加limit值
     * @param selectBody
     * @param l
     */
    public static void setLimit(SelectBody selectBody,long l){
        if(selectBody instanceof PlainSelect){
            Limit limit = new Limit();
            limit.setRowCount(new LongValue(String.valueOf(l)));
            ((PlainSelect) selectBody).setLimit(limit);
        }
    }

    /**
     * 获取FromItem不支持子查询操作
     * @param selectBody
     * @return
     */
    public static FromItem getFromItem(SelectBody selectBody){
        if(selectBody instanceof PlainSelect){
            FromItem fromItem = ((PlainSelect) selectBody).getFromItem();
            return fromItem;
        }else if(selectBody instanceof WithItem){
            SqlParserTool.getFromItem(((WithItem) selectBody).getSelectBody());
        }
        return null;
    }

    /**
     * 获取子查询
     * @param selectBody
     * @return
     */
    public static SubSelect getSubSelect(SelectBody selectBody){

        if(selectBody instanceof PlainSelect){
            FromItem fromItem = ((PlainSelect) selectBody).getFromItem();
            if(fromItem instanceof SubSelect){
                return ((SubSelect) fromItem);
            }
        }else if(selectBody instanceof WithItem){
            SqlParserTool.getSubSelect(((WithItem) selectBody).getSelectBody());
        }
        return null;
    }

    /**
     * 解析insert语句，并生成回滚语句
     * @param insertStame
     * @return
     */
    public static String insertToDelete(Insert insertStame) {

        ExpressionList itemsList = (ExpressionList) insertStame.getItemsList();
        List<Expression> expressions = itemsList.getExpressions();
        String tableName = insertStame.getTable().getName();
        String schemaName = insertStame.getTable().getSchemaName();
        List<Column> columns = insertStame.getColumns();
        StringBuffer sb = new StringBuffer();
        String and = " and ";
        // 生成时需要忽略的关键字。
        String[] filter = {"''", "data", "null", "NULL", "nextval", "NEXTVAL", "select", "XML", "xml", "sysdate", "SYSDATE", "date", "DATE", "select", "SELECT"};

        for (int i=0; i<columns.size(); i++) {
            boolean falg = true;
            for (int j=0; j<filter.length; j++) {

                if (expressions.get(i).toString().contains(filter[j])) {
                    falg = false;
                    break;
                }
            }

            if (falg) {
                sb.append(columns.get(i).getColumnName()).append("=").append(expressions.get(i)).append(and);
            }
        }
        String term = sb.toString();

        if (term.endsWith(and)) {
            term = term.substring(0,sb.length() -and.length());
        }
        Delete delete = new Delete();
        delete.setTable(new Table(schemaName + "." + tableName));

        String sql = delete.toString() + " where " + term + ";";

        return sql;
    }

    /**
     * 判断是否为多级子查询
     * @param selectBody
     * @return
     */
    public static boolean isMultiSubSelect(SelectBody selectBody){
        if(selectBody instanceof PlainSelect){
            FromItem fromItem = ((PlainSelect) selectBody).getFromItem();
            if(fromItem instanceof SubSelect){
                SelectBody subBody = ((SubSelect) fromItem).getSelectBody();
                if(subBody instanceof PlainSelect){
                    FromItem subFromItem = ((PlainSelect) subBody).getFromItem();
                    if(subFromItem instanceof SubSelect){
                        return true;
                    }
                }
            }
        }
        return false;
    }

    /**
     * 获取查询字段
     * @param selectBody
     * @return
     */
    public static List<SelectItem> getSelectItems(SelectBody selectBody){
        if(selectBody instanceof PlainSelect){
            List<SelectItem> selectItems = ((PlainSelect) selectBody).getSelectItems();
            return selectItems;
        }
        return null;
    }

    /**
     * 获取sql条件字段的字段名
     * @param sql
     * @return
     */
    public static Map<String, Object[]> getColumnNameAndValue(String sql) {

        Map<String, Object[]> columnMap = new HashMap<>();
        try {
            SqlType sqlType = SqlParserTool.getSqlType(sql);
            if (sqlType.equals(SqlType.INSERT)) {
                Insert insertStame = (Insert) SqlParserTool.getStatement(sql);
                ExpressionList itemsList = (ExpressionList) insertStame.getItemsList();
                List<Column> columns = insertStame.getColumns();

                for (int i=0; i<columns.size(); i++) {

                    String[] express = new String[] {"?"};
                    columnMap.put(columns.get(i).getColumnName(), express);
                }

            } else if (sqlType.equals(SqlType.DELETE)) {
                Delete statement = (Delete) SqlParserTool.getStatement(sql);
                Expression where = statement.getWhere();
                Map<String, String> map = new HashMap();
                getDeleteWhereColumn(where, map);

                for (String key :map.keySet()) {
                    String[] express = new String[] {"?"};
                    columnMap.put(key, express);
                }
            } else if (sqlType.equals(SqlType.UPDATE)) {
                Update statement = (Update) SqlParserTool.getStatement(sql);
                List<Column> columns = statement.getColumns();

                for (int i=0; i<columns.size(); i++) {
                    String[] express = new String[] {"?"};
                    columnMap.put(columns.get(i).getColumnName(), express);
                }
            } else if (sqlType.equals(SqlType.SELECT)) {
                Select statement = (Select) SqlParserTool.getStatement(sql);
                SelectBody selectBody = statement.getSelectBody();
                Map<String, String> map = new HashMap<>();
                getSelectWhereColumn(selectBody, map);
                for (String key :map.keySet()) {
                    String[] express = new String[] {"?"};
                    columnMap.put(key, express);
                }
            }
        } catch (Exception e) {
            logger.error("解析sql中where条件异常", e);
        }

        return columnMap;
    }

    /**
     * 获取sql中的表名（复杂sql只获取第一个）
     * @param sql
     * @return
     */
    public static String getSingleTableName(String sql) {
        String tableName = "";
        try {
            SqlType sqlType = SqlParserTool.getSqlType(sql);
            if (sqlType.equals(SqlType.INSERT)) {
                Insert statement = (Insert) SqlParserTool.getStatement(sql);
                tableName = statement.getTable().getName();
            } else if (sqlType.equals(SqlType.DELETE)) {
                Delete statement = (Delete) SqlParserTool.getStatement(sql);
                tableName = statement.getTable().getName();
            } else if (sqlType.equals(SqlType.UPDATE)) {
                Update statement = (Update) SqlParserTool.getStatement(sql);
                tableName = statement.getTables().get(0).getName();
            } else if (sqlType.equals(SqlType.SELECT)) {
                Select statement = (Select) SqlParserTool.getStatement(sql);
                tableName = getTableList(statement).get(0);
            }
        } catch (JSQLParserException e) {
            logger.error("解析sql中的table_name异常",e);
        }

        return tableName;
    }

    /**
     * 获取delete语句的条件
     * @param expression
     * @param map
     */
    private static void getDeleteWhereColumn(Expression expression, Map<String, String> map) {

        if (expression instanceof AndExpression) {

            Expression leftExpression = ((AndExpression) expression).getLeftExpression();
            getDeleteWhereColumn(leftExpression, map);
            Expression rightExpression = ((AndExpression) expression).getRightExpression();
            getDeleteWhereColumn(rightExpression, map);
        } else if (expression instanceof  EqualsTo) {
            map.put(((EqualsTo) expression).getLeftExpression().toString(),((EqualsTo) expression).getRightExpression().toString());
        }
    }

    /**
     * 获取查询语句的条件
     * @param selectBody
     * @param map
     */
    private static void getSelectWhereColumn(SelectBody selectBody, Map<String, String> map) {
        if (selectBody instanceof PlainSelect) {
            Expression where = ((PlainSelect) selectBody).getWhere();
            getDeleteWhereColumn(where, map);
        }
    }

    /**
     *  将sql格式化为sql模板，即 select * from test_table where id = 10 and age > 1; => select * from test_table where id = ? and age > ?;
     * @param rawSql        原始sql
     * @param placeholder   占位符
     * @return
     */
    public static String formatSql(String rawSql, String placeholder) throws JSQLParserException {
        SqlType sqlType = SqlParserTool.getSqlType(rawSql);
        switch (sqlType) {
            case INSERT:
                Insert insertStame = (Insert) SqlParserTool.getStatement(rawSql);
                ExpressionList itemsList = (ExpressionList) insertStame.getItemsList();
                List<Expression> expressions = itemsList.getExpressions();
                for (Expression expression : expressions) {
                    formatSimpleExpression(expression, placeholder);
                }
                return insertStame.toString();
            case UPDATE:
                Update updateStame = (Update) SqlParserTool.getStatement(rawSql);
                List<Expression> updateExpressions = updateStame.getExpressions();
                for (Expression expression : updateExpressions) {
                    formatSimpleExpression(expression, placeholder);
                }
                Expression updateWhere = updateStame.getWhere();
                formatWhere(updateWhere, placeholder);
                return updateStame.toString();
            case DELETE:
                Delete deleteStame = (Delete) SqlParserTool.getStatement(rawSql);
                formatWhere(deleteStame.getWhere(), placeholder);
                return deleteStame.toString();
            case SELECT:
                Select selectStame = (Select) SqlParserTool.getStatement(rawSql);
                SelectBody selectBody = selectStame.getSelectBody();
                if (selectBody instanceof PlainSelect) {
                    formatWhere(((PlainSelect) selectBody).getWhere(), placeholder);
                }
                return selectStame.toString();
        }

        return null;
    }

    /**
     * 格式化where条件
     * @param expression
     * @param placeholder
     */
    private static void formatWhere(Expression expression, String placeholder) {
        if (expression instanceof AndExpression) {
            Expression rightExpression = ((AndExpression) expression).getRightExpression();
            Expression leftExpression = ((AndExpression) expression).getLeftExpression();
            formatWhere(rightExpression, placeholder);
            formatWhere(leftExpression, placeholder);
        } else if (expression instanceof InExpression) {
            ItemsList rightItemsList = ((InExpression) expression).getRightItemsList();
            if (rightItemsList instanceof ExpressionList) {
                for (Expression obj : ((ExpressionList) rightItemsList).getExpressions()) {
                    formatSimpleExpression(obj, placeholder);
                }
            }
        } else if (expression instanceof BinaryExpression){
            formatSimpleExpression(((BinaryExpression) expression).getRightExpression(), placeholder);
        }
    }

    /**
     * 格式化简单expression
     * @param expression
     * @param placeholder
     */
    private static void formatSimpleExpression(Expression expression, String placeholder) {
        if (expression instanceof StringValue) {
            ((StringValue) expression).setValue(placeholder);
        } else if (expression instanceof LongValue) {
            ((LongValue) expression).setStringValue(placeholder);
        }
    }

    public static void main(String[] args) throws JSQLParserException {
        //String sql = "\uFEFFinsert into base.net_srv_service (SERVICE_CODE, SERVICE_NAME, SERVICE_DESC, CHANNEL, PROCESS_CODE, MODIFIER, MODIFY_DATE, STATE, REMARK) values ('aiH5ExportExcelUploadByYb', '车联网插入大账户异步导出通知表', '车联网插入大账户异步导出通知表', 'FUSE', 'aiH5ExportExcelUploadByYb', 'chengzhi', sysdate, 'U', '车联网插入大账户异步导出通知表');";
        //String sql = "delete from cz_table where name='cz' and age=18 and home='sh'";
/*        String sql = "insert into auto_collect_test_case_sql (busi_code, run_time, run_sql, sync_time, ext1, ext2)\n" +
                "     (  \n" +
                "      select ? as a\n" +
                "      , ? as b\n" +
                "      , ? as c\n" +
                "      , ? as d\n" +
                "      , ? as e\n" +
                "      , ? as f\n" +
                "      from dual\n" +
                "     union all \n" +
                "      select ? as a\n" +
                "      , ? as b\n" +
                "      , ? as c\n" +
                "      , ? as d\n" +
                "      , ? as e\n" +
                "      , ? as f\n" +
                "      from dual\n" +
                "     )";
        SqlType sqlType = SqlParserTool.getSqlType(sql);
        if (sqlType.equals(SqlType.INSERT)) {
            Insert insertStame = (Insert) SqlParserTool.getStatement(sql);
            List<Column> columns = insertStame.getColumns();
            ExpressionList itemsList = (ExpressionList) insertStame.getItemsList();
            List<Expression> expressions = itemsList.getExpressions();
            Map<String, Object> columnMap = new HashMap<>();
            for (int i=0; i<columns.size(); i++) {

                String[] express = new String[] {"?"};
                columnMap.put(columns.get(i).getColumnName(), express);
            }
            System.out.println(columnMap);
        }

        if (sqlType.equals(SqlType.DELETE)) {
            Delete statement = (Delete) SqlParserTool.getStatement(sql);
            Expression where = statement.getWhere();
            Map<String, String> map = new HashMap<>();
            getDeleteWhereColumn(where, map);
            System.out.println(map);
        }

        if (sqlType.equals(SqlType.SELECT)) {
            Select statement = (Select) SqlParserTool.getStatement(sql);
            SelectBody selectBody = statement.getSelectBody();
            Map<String, String> map = new HashMap<>();
            getSelectWhereColumn(selectBody, map);
            System.out.println(map);
        }*/

        String sql = "select * from ad.ca_bill_0_202312 where busi_code in ('test1', 'test2')";
        // String sql = "insert into test_table values('a', 'b', 'c')";
        // String sql = "update test_table set name = 'cz' where id = 1 and age in (18,19,20) and schole like '%12%' and pag > 1";
        String sql1 = formatSql(sql, "?");
        System.out.println(sql1);
    }

}
